NSQL Expressions Overview

Expressions are a very powerful feature of NSQL, which allow the user to view or query 'dynamic' columns that are comprised of combinations of or functions of other columns in a table.

Consider a table of the customers of a bank, each with a current account and a savings account, we require a Domain of all the customers who do not owe the bank money, even if their current account is overdrawn. To do this we need to know the sum of current account balance (let's call the column CAB) and savings account balance (SAB). This is achieved in Engine using the EXPR{} statement in the following way:

SELECT Count(*)

FROM Customer

WHERE EXPR{ CAB + SAB } >= 0;

The contents of the EXPR{} statement can be any mathematical combination of existing columns in the database, therefore:

Immediate values are also allowed and operator precedence is governed by the use of the parenthesis.

The field type of an expression is set by the first column, function or immediate value found. Therefore, if a string is added to a string, the result is a concatenation of the two strings:

EXPR{ “10”+”20” } returns “1020”

EXPR{ 10+20 } returns 30

EXPT{ VAL(“10”) + “20 “ } returns 30

The function VAL() is a numeric function and is therefore implicitly applied to every other text value in the expression.

All intrinsic functions available can be used in expressions. The functions can be stacked and parameters can be either columns or immediate values, so that:

Will give the first two letters of a person's surname, in upper case, after having removed all spaces. Other intrinsic functions, such as AGE, which would normally take a date column and an immediate date value, can be used with two date columns. For example, a table that held a date of birth field DOB and an insurance policy termination date DTERM, we can use expressions to find the number of customers who will be under 60 when their policy expires:

SELECT Count(*)

FROM Customer

WHERE EXPR{ AGE(DOB,DTERM) } < 60;

Expressions can also produce logical values, using 1 for yes (true) or 0 for no (false), e.g.

SELECT Count(*)

FROM Customer

WHERE EXPR{ AGE(DOB,DTERM) < 60 } = 1;

However, this would not be the most efficient use of such an expression for later needs. To find customers under 50 when their policy expires, most of the processing for the first form of this query would already have been completed, but the second form would need to be totally recalculated. Generally an expression should be designed in such a way that it can be used for more than one specific query.

An expression can also be used to modify the type of a column using the VAL(), STRING() and INT() functions. Therefore, a string column that contained numeric values can be converted to a numeric type, so that comparison queries would produce a numeric result and not an alphanumeric result. Remember that the string "2" is considered to be alpha-numerically greater than the string "1000", so if we execute:

SELECT Count(*)

FROM Table

WHERE column > 1000;

Columns with value "2" would appear in our Domain. However, if we apply the VAL() function to the column with:

SELECT Count(*)

FROM Table

WHERE EXPR{ VAL{column) } > 1000;

	Online & Instructor-Led Courses \| Training Videos \| Webinar Recordings
	© Alterian. All Rights Reserved. \| Privacy Policy \| Legal Notice

Database reference guide

NSQL Expressions Overview